package cn.net.msg.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import cn.net.msg.dao.TbScoreDao;
import cn.net.msg.model.ReturnExamRankBean;
import cn.net.msg.model.ReturnRankBean;
import cn.net.msg.model.TbExamFiles;
import cn.net.msg.model.TbScore;
import cn.net.msg.service.base.DaoSupport;

@Component("tbScoreDao")
public class TbScoreDaoImpl extends DaoSupport implements TbScoreDao {

    @Override
    public boolean isExsit(TbScore tbScore) {
        // TODO Auto-generated method stub
        String hql = "select s from TbScore s where s.examFileId = ? and s.userId = ?";
        List<Object> params = new ArrayList<Object>();
        params.add(tbScore.getExamFileId());
        params.add(tbScore.getUserId());
        List list = (List)super.executeQuery(hql, 0, params,0,0);
        int size = list.size();
        return size > 0;
    }

    @Override
    public void save(TbScore tbScore) {
        // TODO Auto-generated method stub
        super.save(tbScore);
    }

    @Override
    public void updateScoreRank(TbScore tbScore) {
        // TODO Auto-generated method stub
        String hql = "update TbScore s set s.scoreScore = ? where s.examFileId = ? and s.userId = ?";
        List<Object> params = new ArrayList<Object>();
        params.add(tbScore.getScoreScore());
        params.add(tbScore.getExamFileId());
        params.add(tbScore.getUserId());
        super.executNonQuery(hql, params);
    }

    @Override
    public ReturnRankBean getScoreRank(long userid, String examFileIdsString) {
        // TODO Auto-generated method stub
        ReturnRankBean rankBean = new ReturnRankBean();
        //判断此人是否提交过分数，若未提交全球排名为-1
        String sql_1 = "select a.sumscore as sumscore from " +
        "(select t.user_id,sum(t.score_score) sumscore from tb_score t group by t.user_id order by sumscore asc) a " +
        "where a.user_id = ?";
        String sumscore = (String)super.executeSumscoreNativeQuery(sql_1, userid);
        if (sumscore == null) {
            rankBean.setGobalRank("-1");
        } else {
            String sql_2 = "select count(*) as gobalrank from ( " +
            "select t.user_id,sum(t.score_score) sumscore from tb_score t group by t.user_id order by sumscore asc) a " +
            "where (select a.sumscore from ( " +
            "select t.user_id,sum(t.score_score) sumscore from tb_score t group by t.user_id order by sumscore asc) a " +
            "where a.user_id = ?) < a.sumscore";
            Integer gobalRank = (Integer)super.executeGobalRankNativeQuery(sql_2, userid);
            rankBean.setGobalRank(String.valueOf(gobalRank + 1));

            //获取各个考卷的排名
            ArrayList<ReturnExamRankBean> examRanks = new ArrayList<ReturnExamRankBean>();
            String[] examFileIds = examFileIdsString.split(","); 
            for(String examFileId : examFileIds ){
                ReturnExamRankBean returnExamRankBean = new ReturnExamRankBean();
                String sql_3 = "select count(*) as examRank from (" +
                "select t.exam_fileid,t.user_id,t.score_score from tb_score t where t.exam_fileid = ? and t.user_id = ?) a " +
                "where a.score_score < " +
                "(select max(CAST(o.score_score AS UNSIGNED)) from tb_score o where o.exam_fileid = ? )";
                Integer examRank = (Integer)super.executeExamRankNativeQuery(sql_3, userid, examFileId);

                TbExamFiles tbExamFiles = super.query(TbExamFiles.class,Integer.parseInt(examFileId));
                returnExamRankBean.setExamFileId(examFileId);
                returnExamRankBean.setExamTitle(tbExamFiles.getExamTitle());
                returnExamRankBean.setExamInfo(tbExamFiles.getExamDescription());
                returnExamRankBean.setExamRank(String.valueOf(examRank + 1));                

                examRanks.add(returnExamRankBean);
            }
            rankBean.setExamRanks(examRanks);
        }
        return rankBean;
    }
}
